from sklearn import datasets
from sklearn.decomposition import PCA
# import pandas as pd
import numpy as np
import csv
import pymysql
import time
import os
import _thread
from App.FlyInfoDAO import FlyInfoDAO

# coon = pymysql.connect(host='127.0.0.1', user='root',
#                        passwd='fan0314@', port=3306, db='fly_info', charset='utf8')
# cur = coon.cursor()  # 建立游标

FlyInfoDAO = FlyInfoDAO()


class Util():
    def exceptSql(self, sql):
        try:
            cur.execute(sql)
            res = cur.fetchall()
            return res
        except:
            return {}

    def str2sec(self, x):
        h, m = x.split(':')
        return int(h) * 3600 + int(m) * 60

    def cn2sec(self, x):
        x = x.replace('小时', ':')
        x = x.replace('分钟', '')
        h, m = x.split(':')
        n = m
        if m == '':
            n = 0
        return int(h) * 3600 + int(n) * 60

    def changeEat(self, x):
        if x == '无餐食':
            return 0
        else:
            return 1


# sql = 'SELECT arrTime, depTime, distance, flightTime, mealDesc, minPrice FROM wbdflightlist WHERE searchArrivalAirport = \'上海\'	AND searchDepartureAirport = \'深圳\''
# flys = Util().exceptSql(sql)

# fly_list = []
# for fly in flys:
#     start_time = Util().str2sec(fly[0])
#     end_time = Util().str2sec(fly[1])
#     time = Util().cn2sec(fly[3])
#     eat = Util().changeEat(fly[4])
#     fly_list.append([start_time, end_time, time, eat, fly[5]])

# # pca = PCA(n_components='mle')
# pca = PCA(n_components=5)
# new_X = pca.fit_transform(fly_list)
# # print(new_X)
# print(pca.explained_variance_ratio_)

# print(pca.components_)

def getFlys():
    flys = FlyInfoDAO.queryPCs()
    fly_list = []
    for fly in flys:
            start_time = Util().str2sec(fly[0])
            end_time = Util().str2sec(fly[1])
            time = Util().cn2sec(fly[3])
            eat = Util().changeEat(fly[4])
            price = fly[5]
            score = fly[6]
            fly_list.append([start_time, end_time, time, eat, price, score])
    return fly_list
  
def getFlySim():
    flys = FlyInfoDAO.queryPCs()
    fly_list = []
    for fly in flys:
            start_time = Util().str2sec(fly[0])
            end_time = Util().str2sec(fly[1])
            time = Util().cn2sec(fly[3])
            eat = Util().changeEat(fly[4])
            price = fly[5]
            fly_list.append([start_time, end_time, time, eat, price])
    return fly_list


class PACDAO():
    # 获取PC
    def getPC(self):
        fly_list = getFlySim()
        pca = PCA(n_components=5)
        pca.fit_transform(fly_list)
        return pca.explained_variance_ratio_

    def getScatter(self):
        fly_list = getFlySim()
        pca = PCA(n_components=2)
        new_X = pca.fit_transform(fly_list)
        return new_X

    # 获取特征值
    def getComponents(self):
        fly_list = getFlySim()
        pca = PCA(n_components=2)
        new_X = pca.fit_transform(fly_list)
        return pca.components_
    
    def getFlys(self):
        return getFlys()
